#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
备份数据库（仅备份受影响的表）
在执行清理操作前，必须先运行此脚本进行备份
"""

import pymysql
import sys
import os
from datetime import datetime
import json

# 添加父目录到路径
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from crawler.config.settings import DATABASE_CONFIG

def backup_tables():
    conn = pymysql.connect(**DATABASE_CONFIG)
    cursor = conn.cursor()
    
    # 创建备份目录
    backup_dir = os.path.join(os.path.dirname(__file__), 'backups')
    os.makedirs(backup_dir, exist_ok=True)
    
    timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
    
    print("=" * 100)
    print(f"数据库备份 - {timestamp}")
    print("=" * 100)
    
    # 备份受影响彩种的数据
    lottery_types = ['ssq', 'fc3d', 'qlc', 'kl8']
    
    for lottery_type in lottery_types:
        print(f"\n备份 {lottery_type} 的数据...")
        
        # 查询所有记录
        cursor.execute("""
            SELECT id, lottery_type, period, draw_date, all_numbers, 
                   prize_info, province_prizes, pool_amount, sales_amount
            FROM lottery_results
            WHERE lottery_type = %s
            ORDER BY period
        """, (lottery_type,))
        
        results = cursor.fetchall()
        
        if results:
            backup_file = os.path.join(backup_dir, f'lottery_results_{lottery_type}_{timestamp}.json')
            
            backup_data = []
            for row in results:
                backup_data.append({
                    'id': row[0],
                    'lottery_type': row[1],
                    'period': row[2],
                    'draw_date': str(row[3]),
                    'all_numbers': row[4],
                    'prize_info': row[5],
                    'province_prizes': row[6],
                    'pool_amount': float(row[7]) if row[7] else None,
                    'sales_amount': float(row[8]) if row[8] else None
                })
            
            with open(backup_file, 'w', encoding='utf-8') as f:
                json.dump(backup_data, f, ensure_ascii=False, indent=2)
            
            print(f"  ✅ 已备份 {len(results)} 条记录到: {backup_file}")
        else:
            print(f"  ⚠️  没有找到 {lottery_type} 的数据")
    
    # 备份遗漏数据
    print(f"\n备份遗漏数据（lottery_omission）...")
    cursor.execute("""
        SELECT id, lottery_type, period, number_zone, position, number, omission
        FROM lottery_omission
        WHERE lottery_type IN ('ssq', 'fc3d', 'qlc', 'kl8')
        ORDER BY lottery_type, period
    """)
    
    results = cursor.fetchall()
    
    if results:
        backup_file = os.path.join(backup_dir, f'lottery_omission_{timestamp}.json')
        
        backup_data = []
        for row in results:
            backup_data.append({
                'id': row[0],
                'lottery_type': row[1],
                'period': row[2],
                'number_zone': row[3],
                'position': row[4],
                'number': row[5],
                'omission': row[6]
            })
        
        with open(backup_file, 'w', encoding='utf-8') as f:
            json.dump(backup_data, f, ensure_ascii=False, indent=2)
        
        print(f"  ✅ 已备份 {len(results)} 条遗漏记录到: {backup_file}")
    
    print(f"\n{'=' * 100}")
    print(f"✅ 备份完成！备份文件保存在: {backup_dir}")
    print('=' * 100)
    
    cursor.close()
    conn.close()
    
    return backup_dir

if __name__ == '__main__':
    backup_dir = backup_tables()
    print("\n下一步：运行 03_clean_invalid_records.py 清理无效记录")

